﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Text;
using System.IO;
using System.Data.SqlClient;
//using System.Data.SqlServerCe;
using System.Data;
using System.Data.OleDb;
//using ErikEJ.Sql;
using System.Net.Mail;
using System.Net;


namespace PAPI_Import
{
    public static class DataRecordExtensions
    {
        public static bool HasColumn(this IDataRecord dr, string columnName)
        {
            for (int i = 0; i < dr.FieldCount; i++)
            {
                if (dr.GetName(i).Equals(columnName, StringComparison.InvariantCultureIgnoreCase))
                    return true;
            }
            return false;
        }
    }
    
    partial class Program
    {
        static ArrayList adminRecepients = new ArrayList();
        static int PapiImportWorkMode;
        static StreamWriter writer;
        static DateTime CurrDate = System.DateTime.Now;//.ToString("yyyy.MM.dd");
        //static string sSqlConnectionString = @"Data Source=|DataDirectory|\POs.sdf";
        static string sSqlConnectionString = @"Server=localhost;Database=PAPIDB;User Id=sa;Password=1234567;";

        static string sSAPFilename = "SAPExportNew.xls";
        static string sSheetName = "Sheet1";

        enum MailType
        {
            ErrorMail,
            WarningMail
        };

        enum HigherType
        {
            CostCenter,
            InternalOrder
        };

        enum TableType
        {
            POes,
            PO_Import
        };

        enum UserType
        {
            User,
            Cost_Center,
            Int_Order,
            Superuser,
            Administrator,
            TSSC
        };


        static void PapiImportSendMail(MailType mailType, string one_recepient, string body, bool duplicateToSuperUsers)
        {
            PapiImportSendMail(mailType, one_recepient, "Message from POMP System", body, duplicateToSuperUsers);
        }


        static void PapiImportSendMail(MailType mailType, string one_recepient, string subject, string body, bool duplicateToSuperUsers)
        {
            ArrayList tempArrayList = new ArrayList();
            tempArrayList.Add(one_recepient);
            PapiImportSendMail(mailType, tempArrayList, subject, body, duplicateToSuperUsers);
        }

        static void PapiImportSendMail(MailType mailType, ArrayList recepients, string body,bool duplicateToSuperUsers)
        {
            PapiImportSendMail(mailType, recepients, "Message from POMP System", body, duplicateToSuperUsers);
        }
        
        static void PapiImportSendMail(MailType mailType, ArrayList recepients, string subject, string body, bool duplicateToSuperUsers)
        {
            /*
            TextWriter tw = new StreamWriter("C:\\webs\\JustMVCTest\\PAPI_Import\\App_Data\\mail_log.txt",true);
            tw.WriteLine("*****");
            tw.WriteLine(DateTime.Now);
            tw.WriteLine("Subject:");
            tw.WriteLine(subject);
            tw.WriteLine("Body:");
            tw.WriteLine(body);
            tw.WriteLine("recepients:");

            foreach (String st in recepients)
            {
                tw.WriteLine(st);
            }

            tw.Close();

            return; //TODO: REMOVE THIS
            */
            
            var client = new SmtpClient("smtp.gmail.com", 587)
            {
                Credentials = new NetworkCredential("papiproj@gmail.com", "0545357710"),
                EnableSsl = true
            };

            MailMessage myMessage = new MailMessage();
            myMessage.From = new MailAddress("papiproj@gmail.com");
            
            foreach (string recepient in recepients)
            {
                myMessage.To.Add(recepient);
            }

            if (duplicateToSuperUsers)
            {
                ArrayList superUsersArrayList = getSpecialUsersEmails(UserType.Superuser);

                foreach (string recepient in superUsersArrayList)
                {
                    myMessage.CC.Add(recepient);
                }
            }
            
            myMessage.Subject = subject;
            myMessage.Body = body;

            client.Send(myMessage);
            
            //client.Send("papiproj@gmail.com", "tzlilrl@gmail.com", "test", "Dear User, \n The PO: " + po.PONumber.ToString() + " has recieved a postponment for delivery date to date: " + po.DeliveryDate.ToString() + "\n Thank you and have a pleasant week, \n POMP SYSTEM ");
            
            
            return;
        }

        static void importDataFromExcel(string excelFilePath, TableType tableType)
        {
            // make sure your sheet name is correct, here sheet name is Sheet1, so you can change your sheet name if have different

            string myExcelDataQuery;
            if (tableType == TableType.POes)
            {
                myExcelDataQuery = "Select PONumber,Item,Update_Num,latest,Status,Requisitioner,DeliveryDate,DeliveryDate_Req,Cost_Cent,Int_Order,Prof_Cent,Percentage,cost_elem,co_el_name,Vendor,VendorName,Descr,UM,T_Curr,Sch_Lin,Ord_Qty,Open_Qty,Open_Amt_T,Order_Amt,Open_Amt_L,Insert_Date,Update_Date from [" + sSheetName + "$]";
           }
            else // tableType == TableType.PO_Import
            {
                myExcelDataQuery = "Select [Cost#Cent],[Int# Order],[Prof#Cent#],[cost elem],[co#el#name],[vendor],[PO Number],[Item],[Descr#],[UM],[T#Curr],[Vendor Name],[Sch#Lin#],[Requisitio],[Ord#Qty],[Open#Qty],[Order Amt#],[Open Amt#T],[Open Amt#L],[Delivery D],[Purchase O] from [" + sSheetName + "$]";
            }

            //Execute a query to erase any previous data from our destination table
            string sClearSQL = "DELETE FROM " + tableType.ToString();
            SqlConnection SqlConn = new SqlConnection(sSqlConnectionString);
            SqlCommand SqlCmd = new SqlCommand(sClearSQL, SqlConn);
            SqlConn.Open();
            SqlCmd.ExecuteNonQuery();
            SqlConn.Close();


            string sExcelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFilePath + ";Extended Properties=" + "\"Excel 8.0;HDR=YES;\"";
            //Series of commands to bulk copy data from the excel file into our SQL table
            OleDbConnection OleDbConn = new OleDbConnection(sExcelConnectionString);
            OleDbCommand OleDbCmd = new OleDbCommand(myExcelDataQuery, OleDbConn);
            OleDbConn.Open();
            OleDbDataReader dr = OleDbCmd.ExecuteReader();

            SqlBulkCopy bulkCopy = new SqlBulkCopy(sSqlConnectionString);
            bulkCopy.DestinationTableName = tableType.ToString();


            System.Diagnostics.Debug.WriteLine("ending fields");

            //SqlBulkCopyColumnMapping mapCostCent = new SqlBulkCopyColumnMapping("[Cost#Cent]", "Cost_Cent2");
            //bulkCopy.ColumnMappings.Add(mapCostCent);

            //SqlBulkCopyColumnMapping mapCost_Cent = new SqlBulkCopyColumnMapping(0, "Cost_Cent");

            bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(0, "Cost_Cent"));
            bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(1, "Int_Order"));
            bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(2, "Prof_Cent"));
            bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(3, "cost_elem"));
            bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(4, "co_el_name"));
            bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(5, "Vendor"));
            bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(6, "PONumber"));
            bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(7, "Item"));
            bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(8, "Descr"));
            bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(9, "UM"));
            bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(10, "T_Curr"));
            bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(11, "VendorName"));
            bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(12, "Sch_Lin"));
            bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(13, "Requisitio"));
            bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(14, "Ord_Qty"));
            bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(15, "Open_Qty"));
            bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(16, "Order_Amt"));
            bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(17, "Open_Amt_T"));
            bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(18, "Open_Amt_L"));
            bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(19, "DeliveryDate"));
            bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(20, "Purchase_O"));

            bulkCopy.WriteToServer((System.Data.IDataReader)dr);

            /*
            int counter = 0;
            while ((!dr.IsClosed) && dr.Read())
            {
                counter++;
                bulkCopy.WriteToServer((System.Data.IDataReader)dr);
            }
             */

            //SqlConn.Close();
            OleDbConn.Close();
        }

        static void writeToLog(string Message)
        {
            writer.WriteLine(System.DateTime.Now + ": " + Message);
        }

        static ArrayList getSpecialUsersEmails(UserType userType)
        {
            Dictionary<UserType,String> myDic = new Dictionary<UserType,String>();

            myDic[UserType.User] = "na";
            myDic[UserType.Cost_Center] = "cost_center is not null";
            myDic[UserType.Int_Order] = "int_order is not null";
            myDic[UserType.Superuser] = "is_superuser = 1";
            myDic[UserType.Administrator] = "is_administrator = 1";
            myDic[UserType.TSSC] = "is_tssc = 1";

            SqlConnection SqlConn = new SqlConnection(sSqlConnectionString);

            string sClearSQL = "SELECT email FROM Papi_Users WHERE " + myDic[userType];
            SqlCommand SqlCmd = new SqlCommand(sClearSQL, SqlConn);
            SqlConn.Open();

            SqlDataReader dr = SqlCmd.ExecuteReader();

            ArrayList myArrayList = new ArrayList();

            while (dr.Read())
            {
                myArrayList.Add(dr.GetString(0));
            }

            dr.Close();
            SqlConn.Close();

            if (myArrayList.Count == 0)
            {
                throw new NoUsersException();
            }

            return myArrayList;
        }



        static string getEmailForCCorIO(UserType userType,string idstring)
        {

            SqlConnection SqlConn = new SqlConnection(sSqlConnectionString);

            string sClearSQL;

            if (userType == UserType.Int_Order)
            {
                sClearSQL = "SELECT email FROM Papi_Users WHERE Int_Order='" + idstring + "'";
            }
            else // userType == UserType.Cost_Cent
            {
                sClearSQL = "SELECT email FROM Papi_Users WHERE cost_cent='" + idstring + "'";
            }
           
            SqlCommand SqlCmd = new SqlCommand(sClearSQL, SqlConn);
            SqlConn.Open();

            SqlDataReader dr = SqlCmd.ExecuteReader();

            if (dr.Read() == true)
            {
                string returnValue = dr.GetString(0);
                dr.Close();
                SqlConn.Close();
                return returnValue;
            }
            else
            {
                dr.Close();
                SqlConn.Close();
                throw new NoUsersException();
            }
        }

    }
}
